﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;

using Newtonsoft.Json;
using SH.STD.Model;
using Cmn;
using SH.STD.DataProvider;
using System.Web.SessionState;

namespace SH.STD.Web.Handler
{
    /// <summary>
    /// Summary description for Employee
    /// </summary>
    public class Employee : IHttpHandler, IRequiresSessionState
    {
        public void ProcessRequest(HttpContext context)
        {
            context.Response.Cache.SetNoStore();
            context.Response.ContentType = "application/json;charset=gb2312";

            switch (context.Request.Params["action"])
            {
                case "loadData":
                    context.Response.Write(ConvertToJsonString(context.Request.Params["id"], context.Request.Params["category"]));
                    break;
                case "loadDataByDepID":
                    context.Response.Write(GetEmployeesByDepartmentID(context.Request.Params["depID"]));
                    break;
                case "addData":
                    context.Response.Write(AddData(context.Request.Params["jsonDataString"]));
                    break;
                case "removeData":
                    context.Response.Write(RemoveData(context.Request.Params["jsonDataString"]));
                    break;
                case "updateData":
                    context.Response.Write(UpdateData(context.Request.Params["jsonDataString"]));
                    break;
            }
        }

        private string AddData(string jsonDataString)
        {
            JavaScriptObject item = (JavaScriptObject)JavaScriptConvert.DeserializeObject(jsonDataString);
            var sql = @"
INSERT INTO Employee (DepID,Category,Name,BirthDate,Gender,Education,Title,TelPhone,
                      CellPhone,Email,IdentityNo,GraduateSchool,Profession,WorkProfession,Address,Job,JobExperience,JobAchievement,Memo,LastModifiedBy)
            VALUES (@DepID ,@Category ,@Name ,@BirthDate ,@Gender ,@Education ,@Title,@TelPhone ,
                    @CellPhone ,@Email ,@IdentityNo ,@GraduateSchool,@Profession,,@WorkProfession,@Address,@Job,@JobExperience,@JobAchievement,@Memo,@LastModifiedBy);SELECT @@identity";
            var cmdParas = new SqlParameter[]
                    {
                        new SqlParameter("DepID",RepsonseInfo.Decode(item["DepID"].ToString())),
                        new SqlParameter("Name",RepsonseInfo.Decode(item["Name"].ToString())),
                        new SqlParameter("Category",item["Category"]),
                        new SqlParameter("BirthDate",String.IsNullOrEmpty(item["BirthDate"].ToString())?DBNull.Value: item["BirthDate"]),
                        new SqlParameter("Gender",RepsonseInfo.Decode(item["Gender"].ToString())),
                        new SqlParameter("Education",RepsonseInfo.Decode(item["Education"].ToString())),
                        new SqlParameter("Title",RepsonseInfo.Decode(item["Title"].ToString())),
                        new SqlParameter("TelPhone",RepsonseInfo.Decode(item["TelPhone"].ToString())),
                        new SqlParameter("Email",RepsonseInfo.Decode(item["Email"].ToString())),
                        new SqlParameter("CellPhone",RepsonseInfo.Decode(item["CellPhone"].ToString())),
                        new SqlParameter("IdentityNo",RepsonseInfo.Decode(item["IdentityNo"].ToString())),
                        new SqlParameter("GraduateSchool",RepsonseInfo.Decode(item["GraduateSchool"].ToString())),
                        new SqlParameter("Profession",RepsonseInfo.Decode(item["Profession"].ToString())),
                        new SqlParameter("WorkProfession",RepsonseInfo.Decode(item["WorkProfession"].ToString())),
                        new SqlParameter("Address",RepsonseInfo.Decode(item["Address"].ToString())),
                        new SqlParameter("Job",RepsonseInfo.Decode(item["Job"].ToString())),
                        new SqlParameter("JobExperience",RepsonseInfo.Decode(item["JobExperience"].ToString())),
                        new SqlParameter("JobAchievement",RepsonseInfo.Decode(item["JobAchievement"].ToString())),
                        new SqlParameter("Memo",RepsonseInfo.Decode(item["Memo"].ToString())),
                        new SqlParameter("LastModifiedBy",Cmn.Session.GetUserID())
                    };
            var ID = DB.getFieldValue(sql, cmdParas);
            return JavaScriptConvert.SerializeObject(new RepsonseInfo { MSG = ID });
        }

        private string UpdateData(string jsonDataString)
        {
            JavaScriptObject item = (JavaScriptObject)JavaScriptConvert.DeserializeObject(jsonDataString);
            var sql = @"
Update Employee Set 
DepID=@DepID,
Name=@Name,
Category=@Category,
BirthDate=@BirthDate,
Gender=@Gender,
Education=@Education,
Title=@Title,
TelPhone=@TelPhone,
CellPhone=@CellPhone,
Email=@Email,
IdentityNo=@IdentityNo,
GraduateSchool=@GraduateSchool,
Profession=@Profession,
WorkProfession=@WorkProfession,
Address=@Address,
Job=@Job,
JobExperience=@JobExperience,
JobAchievement=@JobAchievement,
Memo=@Memo,
LastModifiedBy=@LastModifiedBy
WHERE ID=@ID";
            var cmdParas = new SqlParameter[]
                    {
                        new SqlParameter("ID",RepsonseInfo.Decode(item["ID"].ToString())),
                        new SqlParameter("DepID",RepsonseInfo.Decode(item["DepID"].ToString())),
                        new SqlParameter("Name",RepsonseInfo.Decode(item["Name"].ToString())),
                        new SqlParameter("Category",item["Category"]),
                        new SqlParameter("BirthDate",String.IsNullOrEmpty(item["BirthDate"].ToString())?DBNull.Value: item["BirthDate"]),
                        new SqlParameter("Gender",RepsonseInfo.Decode(item["Gender"].ToString())),
                        new SqlParameter("Education",RepsonseInfo.Decode(item["Education"].ToString())),
                        new SqlParameter("Title",RepsonseInfo.Decode(item["Title"].ToString())),
                        new SqlParameter("TelPhone",RepsonseInfo.Decode(item["TelPhone"].ToString())),
                        new SqlParameter("Email",RepsonseInfo.Decode(item["Email"].ToString())),
                        new SqlParameter("CellPhone",RepsonseInfo.Decode(item["CellPhone"].ToString())),
                        new SqlParameter("IdentityNo",RepsonseInfo.Decode(item["IdentityNo"].ToString())),
                        new SqlParameter("GraduateSchool",RepsonseInfo.Decode(item["GraduateSchool"].ToString())),
                        new SqlParameter("Profession",RepsonseInfo.Decode(item["Profession"].ToString())),
                        new SqlParameter("WorkProfession",RepsonseInfo.Decode(item["WorkProfession"].ToString())),
                        new SqlParameter("Address",RepsonseInfo.Decode(item["Address"].ToString())),
                        new SqlParameter("Job",RepsonseInfo.Decode(item["Job"].ToString())),
                        new SqlParameter("JobExperience",RepsonseInfo.Decode(item["JobExperience"].ToString())),
                        new SqlParameter("JobAchievement",RepsonseInfo.Decode(item["JobAchievement"].ToString())),
                        new SqlParameter("Memo",RepsonseInfo.Decode(item["Memo"].ToString())),
                        new SqlParameter("LastModifiedBy",Cmn.Session.GetUserID())
                    };
            DB.execSql(sql, cmdParas);
            return JavaScriptConvert.SerializeObject(new RepsonseInfo { MSG = RepsonseInfo.Decode(item["ID"].ToString()) });
        }

        private string ConvertToJsonString(string id, string category)
        {
            var sql =
@"SELECT a.ID,a.Name,b.ID as DepID,b.Name as DepName,a.Gender,a.Education,a.Title,a.BirthDate,a.TelPhone,a.CellPhone,
         a.Email,a.IdentityNo,a.GraduateSchool,a.Profession,a.WorkProfession,a.Address,a.Job,a.JobExperience,
         a.JobAchievement,a.Memo,a.LastChanged,a.LastModifiedBy
  FROM Employee a
  JOIN Department b
  ON a.DepID=b.ID
  WHERE a.ID=@ID";

            var cmdParams = new SqlParameter[]
                {
                    new SqlParameter("ID",id.ToInt())
                };
            var dt = Cmn.DB.getDataTable(sql, cmdParams);
            return DataAccess.ConvertToJSON(dt, "Employees");
        }

        private string GetEmployeesByDepartmentID(string depID)
        {
            var sql =
@"SELECT a.ID as EmpID,a.Name as EmpName,b.ID as DepID,b.Name as DepName,a.Gender,a.Education,a.Title,a.BirthDate,a.TelPhone,a.CellPhone,
         a.Email,a.IdentityNo,a.GraduateSchool,a.Profession,a.WorkProfession,a.Address,a.Job,a.JobExperience,
         a.JobAchievement,a.Memo,a.LastChanged,a.LastModifiedBy
  FROM Employee a
  JOIN Department b
  ON a.DepID=b.ID
  WHERE b.ID=@DepID";

            var cmdParams = new SqlParameter[]
                {
                    new SqlParameter("DepID",depID.ToInt())
                };
            var dt = Cmn.DB.getDataTable(sql, cmdParams);
            return DataAccess.ConvertToJSON(dt, "Employees");
        }

        private string ConvertAllToJsonString(string category)
        {
            var sql =
@"SELECT a.ID,a.Name,b.ID,b.Name as DepName,a.Gender,a.Education,a.Title,a.BirthDate,a.TelPhone,a.CellPhone,
         a.Email,a.IdentityNo,a.GraduateSchool,a.Profession,a.WorkProfession,a.Address,a.Job,a.JobExperience,
         a.JobAchievement,a.Memo,a.LastChanged,a.LastModifiedBy
  FROM Employee a
  JOIN Department b
  ON a.DepID=b.ID
  WHERE a.Category=@category";

            var cmdParams = new SqlParameter[]
                {
                    new SqlParameter("category",category.ToInt())
                };
            var dt = Cmn.DB.getDataTable(sql, cmdParams);
            return DataAccess.ConvertToJSON(dt, "Employees");
        }

        private string RemoveData(string jsonDataString)
        {
            JavaScriptObject item = (JavaScriptObject)JavaScriptConvert.DeserializeObject(jsonDataString);
            var cmdParas = new SqlParameter[]
                    {
                        new SqlParameter("ID",RepsonseInfo.Decode(item["ID"].ToString())),
                    };
            var sql = "update Employee set status=0 where ID=@ID";
            Cmn.DB.execSql(sql, cmdParas);

            return JavaScriptConvert.SerializeObject(new RepsonseInfo { MSG = item["ID"].ToString() });
        }
        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}